﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.OleDb;
namespace DAO
{
    public class KhachHangDAO
    {
        DataProvider dp;
        public KhachHangDAO()
        {
            dp = new DataProvider();
        }
        public DataTable LayDSKhachHang()
        {
            DataTable table = new DataTable();
            string sql = "select * from KhachHang";
            table = dp.LayBangDuLieu(sql);
            return table;
        }
        public bool ThemKhachHang(KhachHangDTO khdto)
        {
            bool flag = true;
            string sql = "insert into KhachHang(hoten,diachi,dienthoai,email,nokhachhang) values ";
            sql += " ('" + khdto.Hoten + "','" + khdto.Diachi + "','" + khdto.Dienthoai + "','" + khdto.Email + "',0)";
            if (!dp.ThucHienCauTruyVan(sql)) flag = false;
            return flag;
        }
        public bool XoaKhachHang(int makhachhang)
        {
            bool flag = true;
            string sql = "delete from KhachHang where makhachhang = " + makhachhang + "";
            if (!dp.ThucHienCauTruyVan(sql)) flag = false;
            return flag;
        }
        public bool CapNhatKhachHang(KhachHangDTO khdto)
        {
            bool flag = true;
            string sql = "update KhachHang set hoten = '" + khdto.Hoten + "', diachi = '" + khdto.Diachi + "', ";
            sql += " email = '" + khdto.Email + "', dienthoai = '" + khdto.Dienthoai + "', nokhachhang = " + khdto.Nokhachhang + " ";
            sql += " where makhachhang = " + khdto.Makhachhang + "";
            if (!dp.ThucHienCauTruyVan(sql)) flag = false;
            return flag;
        }
        public bool KiemTraKhachHang(int makhachhang)
        {
            bool flag = false;
            string sql = "select * from HoaDon where makhachhang = " + makhachhang + "";
            DataTable table = new DataTable();
            table = dp.LayBangDuLieu(sql);
            if (table.Rows.Count > 0) flag = true;
            return flag;
        }
        public DataTable TimKiemKhachHang(string tenkhachhang)
        {
            DataTable table = new DataTable();
            string sql = "select * from KhachHang where Instr(hoten,'" + tenkhachhang + "')> 0 ";
            table = dp.LayBangDuLieu(sql);
            return table;
        }
        public KhachHangDTO LayMotKhachHang(int makhachhang)
        {
            KhachHangDTO khdto = new KhachHangDTO();
            DataTable table = new DataTable();
            string sql = "select * from KhachHang where makhachhang = " + makhachhang + "";
            table = dp.LayBangDuLieu(sql);
            DataRow dr = table.Rows[0];
            khdto.Makhachhang = makhachhang;
            khdto.Hoten = dr[1].ToString();
            khdto.Dienthoai = dr[3].ToString();
            khdto.Diachi = dr[2].ToString();
            khdto.Email = dr[4].ToString();
            khdto.Nokhachhang = double.Parse(dr[5].ToString());
            return khdto;
        }
        public DataTable LayKHThanhToanHoaDon()
        {
            DataTable table = new DataTable();
            string sql = "select * from KhachHang where makhachhang in (select makhachhang from HoaDon where tinhtrang = 0)";
            table = dp.LayBangDuLieu(sql);
            return table;
        }

    }
}
